package cc.messcat.service.fupin;

import cc.messcat.common.model.Pager;
import cc.messcat.common.service.BaseServiceImpl;
import cc.messcat.dao.fupin.FamilyDao;
import cc.messcat.entity.fupin.Family;
import cc.messcat.entity.fupin.FamilyHistory;
import cc.messcat.entity.system.McUser;
import cc.messcat.util.Collections;
import cc.messcat.util.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.ParseException;
import java.util.*;

@Service
public class FamilyServiceImpl extends BaseServiceImpl<Family> implements FamilyService {

	@Autowired
	private FamilyDao familyDao;

	@Override
	public List queryFaList(Long id) {
		Map map = new HashMap<>();
		String hql = "from Family where poorFamily.id= :id and state= :state";
		map.put("id", id);
		map.put("state", "1");
		return this.familyDao.queryList(hql, map);
	}

	@Override
	public List queryFaAll() {
		Map map = new HashMap<>();
		String hql = "from Family where isDel= :isDel and state= :state and fid IS NULL";
		map.put("isDel", "0");
		map.put("state", "0");
		return this.familyDao.queryList(hql, map);
	}
	
	/**
	 * 生成新实体类的通用方法
	 */
	@Override
	public Family getFamily(Family family){
		Family family0 = new Family();
		family0.setId(family.getId());
		family0.setName(family.getName());
		family0.setNumber(family.getNumber());
		family0.setSex(family.getSex());
	    family0.setIdType(family.getIdType());
	    family0.setIdNumber(family.getIdNumber());
	    family0.setBirthTime(family.getBirthTime());
	    family0.setRelation(family.getRelation());
	    family0.setNation(family.getNation());
	    family0.setPolitical(family.getPolitical());
	    family0.setCulture(family.getCulture());
	    family0.setSchool(family.getSchool());
	    family0.setHealthy(family.getHealthy());
	    family0.setLabour(family.getLabour());
	    family0.setSoldier(family.getSoldier());
	    family0.setLow(family.getLow());
	    family0.setProvince(family.getProvince());
	    family0.setCity(family.getCity());
	    family0.setCounty(family.getCounty());
	    family0.setVillage(family.getVillage());
	    family0.setWorkTime(family.getWorkTime());
	    family0.setCompany(family.getCompany());
	    family0.setTelephone(family.getTelephone());
	    family0.setLarge(family.getLarge());
	    family0.setSchoolName(family.getSchoolName());
	    family0.setEnterTime(family.getEnterTime());
	    family0.setMajor(family.getMajor());
	    family0.setInstitution(family.getInstitution());
	    family0.setIsStudent(family.getIsStudent());
	    family0.setDoType(family.getDoType());
	    family0.setAddTime(family.getAddTime());
	    family0.setUpTime(family.getUpTime());
	    family0.setDeTime(family.getDeTime());
	    family0.setIsDel(family.getIsDel());
		family0.setState(family.getState());
		family0.setFid(family.getFid());
		return family0;
	}
	
	//family0：新实体类       family：旧实体类
	public Family getFamily1(Family family){
		Family family0 = familyDao.get(family.getId());
		family0.setId(family.getId());
		family0.setName(family.getName());
		family0.setNumber(family.getNumber());
		family0.setSex(family.getSex());
	    family0.setIdType(family.getIdType());
	    family0.setIdNumber(family.getIdNumber());
	    family0.setBirthTime(family.getBirthTime());
	    family0.setRelation(family.getRelation());
	    family0.setNation(family.getNation());
	    family0.setPolitical(family.getPolitical());
	    family0.setCulture(family.getCulture());
	    family0.setSchool(family.getSchool());
	    family0.setHealthy(family.getHealthy());
	    family0.setLabour(family.getLabour());
	    family0.setSoldier(family.getSoldier());
	    family0.setLow(family.getLow());
	    family0.setProvince(family.getProvince());
	    family0.setCity(family.getCity());
	    family0.setCounty(family.getCounty());
	    family0.setVillage(family.getVillage());
	    family0.setWorkTime(family.getWorkTime());
	    family0.setCompany(family.getCompany());
	    family0.setTelephone(family.getTelephone());
	    family0.setLarge(family.getLarge());
	    family0.setSchoolName(family.getSchoolName());
	    family0.setEnterTime(family.getEnterTime());
	    family0.setMajor(family.getMajor());
	    family0.setInstitution(family.getInstitution());
	    family0.setIsStudent(family.getIsStudent());
	    family0.setDoType(family.getDoType());
	    family0.setAddTime(family.getAddTime());
	    family0.setUpTime(family.getUpTime());
	    family0.setDeTime(family.getDeTime());
	    family0.setIsDel(family.getIsDel());
		family0.setState(family.getState());
		family0.setFid(family.getFid());
		return family0;
	}

	@Override
	public List queryFaAlls(Long fid) {
		Map map = new HashMap<>();
		String hql = "from Family where isDel= :isDel and state= :state and fid= :fid";
		map.put("isDel", "0");
		map.put("state", "0");
		map.put("fid", fid);
		return this.familyDao.queryList(hql, map);
	}
	
	@Override
	public List queryFamilyByFid(Long fid) {
		Map map = new HashMap<>();
		String hql = "from Family where relation='户主' and isDel= :isDel and fid= :fid";
		map.put("isDel", "0");
		map.put("fid", fid);
		return this.familyDao.queryList(hql, map);
	}

	/**
	 * 贫困人口劳动能力类型统计
	 */
	@Override
	public Pager laborTypeStatistics(McUser user, Integer pageNo, Integer pageSize, String year, String country, String village,
		String poverty) {
		pageNo = pageNo == null || pageNo < 1 ? 1 : pageNo;
		pageSize = pageSize == null || pageSize < 1 ? 12 : pageSize;

		// 权限错误
		if (user.getUserLevel().getLevel() < 0 || user.getUserLevel().getLevel() > 5) {
			return new Pager(pageSize,pageNo,0,null);
		}

		return familyDao.laborTypeStatistics(user,  pageNo,  pageSize,  year,  country,  village,poverty);
	}

	/**
	 * 贫困人口劳动能力类型统计excel导出
	 */
	@Override
	@SuppressWarnings("all")
	public HSSFWorkbook exportLaborTypeStatistics(McUser user, Integer pageNo, Integer pageSize, String year, String country,
		String village, String poverty) {
		// XXX 导出结构优化
		String[] cellName = new String[] { "序号", "地区", "贫困人口", "普通劳动力人数", "普通劳动力比例", "技能劳动力人数", "技能劳动力比例", "丧失劳动力人数", "丧失劳动力比例",
			"无劳动力人数", "无劳动力比例"};
		String[] field = new String[] { "地区", "贫困人口", "普通劳动力人数", "普通劳动力比例", "技能劳动力人数", "技能劳动力比例", "丧失劳动力人数", "丧失劳动力比例", "无劳动力人数",
			"无劳动力比例" };

		List list = new ArrayList<>();
		Map map = null;

		for (Map e : (List<Map>) laborTypeStatistics(user, 1, 5000, year, country, village, poverty).getResultList()) {
			map = new LinkedHashMap<>(30);
			map.put("地区", e.get("area"));
			map.put("贫困人口", e.get("total"));
			map.put("普通劳动力人数", (e.get("pt")) == null ? "0" : ((Map) e.get("pt")).get("count"));
			map.put("普通劳动力比例", (e.get("pt")) == null ? "0" : ((Map) e.get("pt")).get("percentage"));
			map.put("技能劳动力人数", ( e.get("jn")) == null ? "0" : ((Map) e.get("jn")).get("count"));
			map.put("技能劳动力比例", (e.get("jn")) == null ? "0" : ((Map) e.get("jn")).get("percentage"));
			map.put("丧失劳动力人数", (e.get("ss")) == null ? "0" : ((Map) e.get("ss")).get("count"));
			map.put("丧失劳动力比例", ( e.get("ss")) == null ? "0" : ((Map) e.get("ss")).get("percentage"));
			map.put("无劳动力人数", ( e.get("w")) == null ? "0" : ((Map) e.get("w")).get("count"));
			map.put("无劳动力比例", (e.get("w")) == null ? "0" : ((Map) e.get("w")).get("percentage"));
			map.put("其他人数", (e.get("others")) == null ? "0" : ((Map) e.get("others")).get("count"));
			map.put("其他比例", (e.get("others")) == null ? "0" : ((Map) e.get("others")).get("percentage"));
			list.add(map);
		}

		HSSFWorkbook workbook = new HSSFWorkbook();

		HSSFSheet sheet = workbook.createSheet("贫困人口劳动能力类型统计表");
		sheet.setDefaultColumnWidth(16);

		HSSFCellStyle headStyle = ExcelUtil.getTitleStyle(workbook);
		HSSFCellStyle cellStyle = ExcelUtil.getCellStyle(workbook);
		HSSFCellStyle columnTitleStyle = ExcelUtil.getColumnTitleStyle(workbook);

		ExcelUtil.addTitle(sheet, cellName, "贫困人口劳动能力类型统计表", headStyle, columnTitleStyle);
		ExcelUtil.addContextByList(sheet, list, field, cellStyle, true);
		return workbook;
	}

	/**
	 * 贫困人口健康统计
	 */
	@SuppressWarnings("rawtypes")
	@Override	 

	public Pager popuHealthyStatisPager(Integer pageNo, Integer pageSize,McUser user, String year, String country,String village, String poverty) {
		 return this.familyDao.popuHealthyStatisPager(pageNo, pageSize,user, year, country,village, poverty);
	}
	/**
	 * 贫困人口健康统计EX导出
	 */
	@SuppressWarnings("unchecked")
	@Override
	public HSSFWorkbook exportPopuHealthyStatis(McUser user, Integer pageNo, Integer pageSize, String year,
			String poverty, String country, String village) {
		String[] cellName = new String[]
				{"序号","地区","贫困人口","健康人数","健康占比","长期慢性病人数","长期慢性病占比","患有大病","患有大病占比","疾病人数","疾病占比"};
		String[] field = new String[] 
				{"areaName","popuCount","htCount","htCountP","cqbCount","cqbCountP","dbCount","dbCountP","jbCount","jbCountP"};
		Integer sheetCount = 0;
		Pager pager = null;
		Map<Integer,List> excelResult = new HashMap<>();
		
		do {
			sheetCount++;			
			pager  = this.popuHealthyStatisPager(1, 1000000, user, year, country, village, poverty);
			excelResult.put(sheetCount, pager.getResultList());
			
		} while(sheetCount < (pager.getRowCount()+4999) / 5000);
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		for(int i = 1; i <= excelResult.size(); i++) {
			HSSFSheet sheet = workbook.createSheet("贫困人口身体健康情况统计表" + i);
			sheet.setDefaultColumnWidth(13);
			
			HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);
			ExcelUtil.addTitle(sheet,cellName,"贫困人口身体健康情况统计表",headStyle,columnTitleStyle);
			ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
		}
		
		return workbook;
	
	}
	/**
	 * 贫困人口文化程度统计
	 */
	@Override
	public Pager popuCultureStatisPager(Integer pageNo, Integer pageSize,McUser user, String year, String country,String village, String poverty) {
		return this.familyDao.popuCultureStatisPager( pageNo,  pageSize, user,  year,  country, village,  poverty);
	}
	/**
	 * 贫困人口文化程度统计EX导出
	 */
	@Override
	public HSSFWorkbook exportPopuCultureStatis(McUser user, Integer pageNo, Integer pageSize, String year,
			String country, String village, String poverty) {
		
	 
		
		String[] cellName = new String[]
				{"序号","地区","贫困人口","文盲半文盲人数","文盲半文盲占比","小学人数","小学占比","初中人数","初中占比","高中人数","高中占比","大专及以上人数","大专及以上占比","学龄前儿童人数","学龄前儿童占比"};
		String[] field = new String[] 
				{"areaName","popuCount","wmCount","wmCountP","xxueCount","xxueCountP","czCount","czCountP","gzCount","gzCountP","dzCount","dzCountP","xlqCount","xlqCountP"};
		Integer sheetCount = 0;
		Pager pager = null;
		Map<Integer,List> excelResult = new HashMap<>();
		
		do {
			sheetCount++;
			pager  = this.popuCultureStatisPager(1, 1000000, user, year, country, village, poverty);
			excelResult.put(sheetCount, pager.getResultList());
			
		} while(sheetCount < (pager.getRowCount()+4999) / 5000);
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		for(int i = 1; i <= excelResult.size(); i++) {
			HSSFSheet sheet = workbook.createSheet("贫困人口文化程度情况统计表" + i);
			sheet.setDefaultColumnWidth(13);
			
			HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);
			ExcelUtil.addTitle(sheet,cellName,"贫困人口文化程度情况统计表",headStyle,columnTitleStyle);
			ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
		}
		
		return workbook;
	}

	@Override
	public Boolean getfid(Long id) {
		Map map = new HashMap<>();
		String hql = "from Family where fid= :fid and relation = '户主'";
		map.put("fid", id);
		List<Family> familys = this.familyDao.queryList(hql, map);
		for (int i = 0; i < familys.size(); i++) {
			if(familys.get(i).getRelation().equals("户主")){
				return true;
			}else{
				return false;
			}
		}
		return null;
	}

	@Override
	public Boolean getid(Long id) {
		Family family = this.familyDao.get(id);
		if(family.getRelation().equals("户主")){
			return true;
		}else{
			return false;
		}
		/*Map map = new HashMap<>();
		String hql = "from Family where id= :id and relation = '户主'";
		map.put("id", id);
		return this.familyDao.queryList(hql, map).isEmpty();*/
	}
	/**
	 * 贫困户生产生活条件困难情况统计
	 */

	@Override
	public Pager ductionLifeStatisPager(Integer pageNo, Integer pageSize, McUser user, String year, String country,
			String village, String poverty) {
		return this.familyDao.ductionLifeStatisPager(pageNo,  pageSize,  user,  year,  country,
				 village,  poverty);
	}

	/**
	 * 贫困户生产生活条件困难情况统计EX导出
	 */
	@Override
	public HSSFWorkbook exportDuctionLifeStatis(McUser user, Integer pageNo, Integer pageSize, String year,
			String poverty, String country, String village) {
		String[] cellName = new String[]
				{"序号","地区","贫困户数","饮水困难户数","饮水困难户占比","无安全饮水户数","无安全饮水户占比","未通生活用电户数","未通生活用电户占比","未通广播电视户数","未通广播电视户占比","住房是危房户户数","住房是危房户占比","无卫生厕所户数","无卫生厕所户数占比","人均住房面积"};
		String[] field = new String[] 
				{"areaName","poorFCount","ysknCount","ysknCountP","waqysCount","waqysCountP","wtshydCount","wtshydCountP","wtggdsCount","wtggdsCountP","zfwxCount","zfwxCountP","wwscsCount","wwscsCountP","zfmjCount"};
		Integer sheetCount = 0;
		Pager pager = null;
		Map<Integer,List> excelResult = new HashMap<>();
		
		do {
			sheetCount++;			
			pager  = this.ductionLifeStatisPager(1, 1000000, user, year, country, village, poverty);
			excelResult.put(sheetCount, pager.getResultList());
			
		} while(sheetCount < (pager.getRowCount()+4999) / 5000);
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		for(int i = 1; i <= excelResult.size(); i++) {
			HSSFSheet sheet = workbook.createSheet("贫困户生产生活条件困难情况统计表" + i);
			sheet.setDefaultColumnWidth(13);
			
			HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);
			ExcelUtil.addTitle(sheet,cellName,"贫困户生产生活条件困难情况统计表",headStyle,columnTitleStyle);
			ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
		}
		
		return workbook;
	
	}

	@Override
	public Boolean getfids(Long fid) {
		Map map = new HashMap<>();
		String hql = "from Family where fid= :fid and relation = '户主'";
		map.put("fid", fid);
		return this.familyDao.queryList(hql, map).isEmpty();
	}

	@Override
	public Boolean getids(Long id) {
		Map map = new HashMap<>();
		String hql = "from Family where id= :id and relation = '户主'";
		map.put("id", id);
		return this.familyDao.queryList(hql, map).isEmpty();
	}

	/**
	 * 贫困户土地资源情况统计表
	 */
	@Override
	public Pager landResourceStatisPager(Integer pageNo, Integer pageSize, McUser user, String year, String country,
			String village, String poverty) {
		return this.familyDao.landResourceStatisPager(pageNo,  pageSize,  user,  year,  country,
				 village,  poverty);
	}
	/**
	 * 导出贫困户土地资源情况统计表
	 */
	@SuppressWarnings("unchecked")
	@Override
	public HSSFWorkbook exportLandResourceStatis(McUser user, Integer pageNo, Integer pageSize, String year,
			String poverty, String country, String village) {
		String[] cellName = new String[]
				{"序号","地区","贫困户数","贫困人口","耕地面积(亩)","耕地面积(人均)","有效灌溉面积(亩)","有效灌溉面积(人均)","林地面积(亩)","林地面积(人均)","退耕还林面积(亩)","退耕还林面积(人均)","林果面积(亩)","林果面积(人均)","牧草地面积(亩)","牧草地面积(人均)","水域面积(亩)","水域面积(人均)"};
		String[] field = new String[] 
				{"areaName","poorFCount","popuCount","gdmjCount","gdmjCountP","yxggCount","yxggCountP","ldCount","ldCountP","tghlCount","tghlCountP","lgCount","lgCountP","mcCount","mcCountP","syCount","syCountP"};
		Integer sheetCount = 0;
		Pager pager = null;
		Map<Integer,List> excelResult = new HashMap<>();
		
		do {
			sheetCount++;			
			pager  = this.landResourceStatisPager(1, 1000000, user, year, country, village, poverty);
			excelResult.put(sheetCount, pager.getResultList());
			
		} while(sheetCount < (pager.getRowCount()+4999) / 5000);
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		for(int i = 1; i <= excelResult.size(); i++) {
			HSSFSheet sheet = workbook.createSheet("贫困户土地资源情况统计表" + i);
			sheet.setDefaultColumnWidth(13);
			
			HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);
			ExcelUtil.addTitle(sheet,cellName,"贫困户土地资源情况统计表",headStyle,columnTitleStyle);
			ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
		}
		
		return workbook;
	}
    /**
     * 贫困户人均收入分组情况统计
     */
	@Override
	public Pager capitaIncomeStatisPager(Integer pageNo, Integer pageSize, McUser user, String year, String country,
			String village, String poverty) {
		return this.familyDao.capitaIncomeStatisPager(pageNo,  pageSize,  user,  year,  country,
				 village,  poverty);
	}
   /**
    * 导出贫困户人均收入分组情况统计表
    */
	@SuppressWarnings("unchecked")
	@Override
	public HSSFWorkbook exportCapitaIncomeStatis(McUser user, Integer pageNo, Integer pageSize, String year,
			String poverty, String country, String village) {
		String[] cellName = new String[]
				{"序号","地区","贫困户数","户均纯收入(元)","500元(不含)以下户数","500元(不含)以下占比","500-1000元户数","500-1000元占比","1000-1500元户数","1000-1500元占比","1500-2000元户数","1500-2000元占比","2000-2952元户数","2000-2952元占比","2952-3100元户数","2952-3100元占比","3100元以上户数","3100元以上占比"};
		String[] field = new String[] 
				{"areaName","poorFCount","incomeCount","wbyCount","wbyCountP","yqyCount","yqyCountP","yqwCount","yqwCountP","lqyCount","lqyCountP","ljwlCount","ljwlCountP","sqyCount","sqyCountP","qtCount","qtCountP"};
		Integer sheetCount = 0;
		Pager pager = null;
		Map<Integer,List> excelResult = new HashMap<>();
		
		do {
			sheetCount++;			
			pager  = this.capitaIncomeStatisPager(1, 1000000, user, year, country, village, poverty);
			excelResult.put(sheetCount, pager.getResultList());
			
		} while(sheetCount < (pager.getRowCount()+4999) / 5000);
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		for(int i = 1; i <= excelResult.size(); i++) {
			HSSFSheet sheet = workbook.createSheet("贫困户人均收入分组情况统计表" + i);
			sheet.setDefaultColumnWidth(13);
			
			HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);
			ExcelUtil.addTitle(sheet,cellName,"贫困户人均收入分组情况统计表",headStyle,columnTitleStyle);
			ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
		}
		
		return workbook;
	}

	/**
	 * 燃料类型统计
	 */

	@Override
	public Pager fuelTypeStatisPager(Integer pageNo, Integer pageSize, McUser user, String year, String country,
			String village, String poverty) {
		return this.familyDao.fuelTypeStatisPager(pageNo,  pageSize,  user,  year,  country,
				 village,  poverty);
	}

	/**
	 * 导出燃料类型统计
	 */

	@SuppressWarnings("unchecked")
	@Override
	public HSSFWorkbook exportFuelTypeStatis(McUser user, Integer pageNo, Integer pageSize, String year, String poverty,
			String country, String village) {
		String[] cellName = new String[]
				{"序号","地区","贫困户数","柴草户数","柴草占比","干蓄粪户数","干蓄粪占比","煤炭户数","煤炭占比","清洁能源户数","清洁能源占比","其他户数","其他占比"};
		String[] field = new String[] 
				{"areaName","poorFCount","ccCount","ccCountP","gxfCount","gxfCountP","mtCount","mtCountP","qjnyCount","qjnyCountP","qtCount","qtCountP"};
		Integer sheetCount = 0;
		Pager pager = null;
		Map<Integer,List> excelResult = new HashMap<>();
		
		do {
			sheetCount++;			
			pager  = this.fuelTypeStatisPager(1, 1000000, user, year, country, village, poverty);
			excelResult.put(sheetCount, pager.getResultList());
			
		} while(sheetCount < (pager.getRowCount()+4999) / 5000);
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		for(int i = 1; i <= excelResult.size(); i++) {
			HSSFSheet sheet = workbook.createSheet("贫困户主要燃料分类统计表" + i);
			sheet.setDefaultColumnWidth(13);
			
			HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);
			ExcelUtil.addTitle(sheet,cellName,"贫困户主要燃料分类统计表",headStyle,columnTitleStyle);
			ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
		}
		
		return workbook;
	}

	/**
	 * 贫困户入户路情况分类统计表
	 */
	@Override
	public Pager entranceRoadStatisPager(Integer pageNo, Integer pageSize, McUser user, String year, String country,
			String village, String poverty) {
		return this.familyDao.entranceRoadStatisPager(pageNo,  pageSize,  user,  year,  country,
				 village,  poverty);
	}

	/**
	 * 导出贫困户入户路情况分类统计表
	 */
	@SuppressWarnings("unchecked")
	@Override
	public HSSFWorkbook exportEntranceRoadStatis(McUser user, Integer pageNo, Integer pageSize, String year,
			String poverty, String country, String village) {
		String[] cellName = new String[]
				{"序号","地区","贫困户数","距离主干路","普通泥土路","砂石公路","水泥路面公路","沥青路面公路"};
		String[] field = new String[] 
				{"areaName","poorFCount","jlzgCount","ptntCount","ssglCount","snlmCount","lqglCount"};
		Integer sheetCount = 0;
		Pager pager = null;
		Map<Integer,List> excelResult = new HashMap<>();
		
		do {
			sheetCount++;			
			pager  = this.entranceRoadStatisPager(1, 1000000, user, year, country, village, poverty);
			excelResult.put(sheetCount, pager.getResultList());
			
		} while(sheetCount < (pager.getRowCount()+4999) / 5000);
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		for(int i = 1; i <= excelResult.size(); i++) {
			HSSFSheet sheet = workbook.createSheet("贫困户入户路情况统计表" + i);
			sheet.setDefaultColumnWidth(13);
			
			HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);
			ExcelUtil.addTitle(sheet,cellName,"贫困户入户路情况统计表",headStyle,columnTitleStyle);
			ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
		}
		
		return workbook;
	}

	/**
	 * 贫困户家庭收入情况统计表
	 */
	@Override
	public Pager familyIncomeStatisPager(Integer pageNo, Integer pageSize, McUser user, String year, String country,
			String village, String poverty) {
		return this.familyDao.familyIncomeStatisPager(pageNo,  pageSize,  user,  year,  country,
				 village,  poverty);
	}

	/**
	 * 导出贫困户家庭收入情况统计表
	 */
	@SuppressWarnings("unchecked")
	@Override
	public HSSFWorkbook exportFamilyIncomeStatis(McUser user, Integer pageNo, Integer pageSize, String year,
			String poverty, String country, String village) {
		String[] cellName = new String[]
				{"序号","地区","贫困户数","贫困人口","年收入","户均年收入","户均生产经营性收入","户均务工收入","户均财产性收入","户均转移性收入","户均生产经营性支出","户均纯收入","人均纯收入"};
		String[] field = new String[] 
				{"areaName","poorFCount","popuCount","nsrCount","hjnsrCount","scjyCountS","hjwgCount","hjccCount","hjzyCount","scjyCountZ","hjcCount","rjcCount"};
		Integer sheetCount = 0;
		Pager pager = null;
		Map<Integer,List> excelResult = new HashMap<>();
		
		do {
			sheetCount++;			
			pager  = this.familyIncomeStatisPager(1, 1000000, user, year, country, village, poverty);
			excelResult.put(sheetCount, pager.getResultList());
			
		} while(sheetCount < (pager.getRowCount()+4999) / 5000);
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		for(int i = 1; i <= excelResult.size(); i++) {
			HSSFSheet sheet = workbook.createSheet("贫困户家庭收入情况统计表" + i);
			sheet.setDefaultColumnWidth(13);
			
			HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);
			ExcelUtil.addTitle(sheet,cellName,"贫困户家庭收入情况统计表",headStyle,columnTitleStyle);
			ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
		}
		
		return workbook;
	}

	/**
	 * 贫困户转移性收入构成情况统计表
	 */
	@Override
	public Pager transferableIncStatisPager(Integer pageNo, Integer pageSize, McUser user, String year, String country,
			String village, String poverty) {
		return this.familyDao.transferableIncStatisPager(pageNo, pageSize, user, year, country, village, poverty);
	}

	/**
	 * 导出贫困户转移性收入构成情况统计表
	 */
	@SuppressWarnings("unchecked")
	@Override
	public HSSFWorkbook exportTransferableIncStatis(McUser user, Integer pageNo, Integer pageSize, String year,
			String poverty, String country, String village) {
		String[] cellName = new String[]
				{"序号","地区","贫困户数","户均转移性收入","户均领取计划生育补贴金额金额","户均领取计划生育补贴金额占比","户均领取低保金金额","户均领取低保金占比","户均领取养老金金额","户均领取养老金占比","户均领取生态补偿金金额","户均领取生态补偿金占比"};
		String[] field = new String[] 
				{"areaName","poorFCount","zyxCount","jhsybtCount","jhsybtCountP","dbjCount","dbjCountP","yljCount","yljCountP","stbcjCount","stbcjCountP"};
		Integer sheetCount = 0;
		Pager pager = null;
		Map<Integer,List> excelResult = new HashMap<>();
		
		do {
			sheetCount++;			
			pager  = this.transferableIncStatisPager(1, 1000000, user, year, country, village, poverty);
			excelResult.put(sheetCount, pager.getResultList());
			
		} while(sheetCount < (pager.getRowCount()+4999) / 5000);
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		for(int i = 1; i <= excelResult.size(); i++) {
			HSSFSheet sheet = workbook.createSheet("贫困户转移性收入构成情况统计表" + i);
			sheet.setDefaultColumnWidth(13);
			
			HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);
			ExcelUtil.addTitle(sheet,cellName,"贫困户转移性收入构成情况统计表",headStyle,columnTitleStyle);
			ExcelUtil.addContextByList(sheet, excelResult.get(i), field, cellStyle, true);
		}
		
		return workbook;
	}

	@Override
	public void modifyObject2(Family family) {
		// TODO Auto-generated method stub
		familyDao.modifyObject2(family);
		
	}

	@Override
	public Long addFamily(Family family) {
		// TODO Auto-generated method stub
		return familyDao.addFamily(family);
	}

	@Override
	public Family get(Long sid) {
		
		return this.familyDao.get(sid);
	}

	@Override
	public List<Family> queryFaAll2() {
		Map map = new HashMap<>();
		String hql = "from Family where relation='户主' and isDel= :isDel and state= :state and fid IS NULL";
		map.put("isDel", "0");
		map.put("state", "0");
		return this.familyDao.queryList(hql, map);
	}

	@Override
	public List<Family> queryFamilyByid(Long id) {
		Map map = new HashMap<>();
		String hql = "from Family where id= :id";
		map.put("id", id);
		return this.familyDao.queryList(hql, map);
	}

	@Override
	public List<Family> queryFamilyByfidnull() {
		Map map = new HashMap<>();
		String hql = "from Family where fid is null";
		return this.familyDao.queryList(hql, map);
	}

	@Override
	public List<Family> queryFaAllid() {
		Map map = new HashMap<>();
		String hql = "from Family where fid is null";
		return this.familyDao.queryList(hql, map);
	}

	/**
	 * 根据sid查询family的id拿到family类
	 * @throws ParseException 
	 */
	@Override
	public Family familyBuId(Long sid) {
		// TODO Auto-generated method stub
		return this.familyDao.get(sid);
	}

	@Override
	public Long addFamilyHistory(FamilyHistory familyHistory) {
		return familyDao.addFamilyHistory(familyHistory);
	}

	@Override
	public void merge(Family f) {
		familyDao.merge(f);
	}

    @Override
    public Long getFamilyCount(String annual, Long areaID) {
        return (Long) familyDao
                .queryList(" select count(*) from PoorFamily as pf ,Family as f" +
                        " where f.isDel='0' and pf.isDel='0' and f.annual=pf.annual and f.fid=pf.id and f.annual= :annual and pf.area.id=:areaID", Collections.generateHashMap("annual",annual,"areaID",areaID))
                .get(0);
    }

}
